﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    /// <summary>
    /// 基金銷量
    /// </summary>
    public class DA_WMG007:DA_Matrix
    {

        public DataTable GetSummaryData(string[] p_months,string[] p_allMonths,string p_opt)
        {
            string v1,sql;
            /*
            v1 = string.Format(@"
AND A.SNAP_DATE BETWEEN TO_DATE('{0}','YYYYMMDD') AND TO_DATE('{1}','YYYYMMDD')"
                ,p_months);
             *  */
             
            if (p_opt.Length.Equals(0))
            {
                v1 = string.Format(@"
                AND (A.SNAP_DATE BETWEEN '{0}' AND '{1}')"
                , p_months[0].ToString().Substring(0,6)
                , p_months[1].ToString().Substring(0,6) );        
                sql = string.Format(getSummeryStatement(), v1
                , getFieldString(p_allMonths));
            }
            else
            {
                v1 = string.Format(@"
AND A.SNAP_DATE BETWEEN TO_DATE('{0}','YYYYMMDD') AND TO_DATE('{1}','YYYYMMDD')"
                    , p_months);

                sql = string.Format(getSQLStatementByPAY(), v1 +getOptionCriteria(p_opt)
                    , getFieldString(p_allMonths), getUNIONStatement(p_months[0]));
            }
            return getData(sql, "MONTHLY_TXNS", DBOpt.Oracle); ;
        }

        public DataTable GetTopTenData(string[] p_months, string[] p_allMonths)
        {
            string v1, sql,fld;
            fld = @"
    ,MAX(CASE WHEN A.YM = '{0}' THEN A.基金名稱 END) AS 基金名稱{0}
	,MAX(CASE WHEN A.YM = '{0}' THEN A.銷量 END) AS 銷量{0}
";
            /*
            v1 = string.Format(@"
AND A.SNAP_DATE BETWEEN TO_DATE('{0}','YYYYMMDD') AND TO_DATE('{1}','YYYYMMDD')"
                , p_months);
             */
            v1 = string.Format(@"
AND (A.YM BETWEEN '{0}' AND '{1}')"
                , p_months);

            sql = string.Format(getTopTenStatement()
                , v1,getFieldString(p_allMonths,fld));
            return getData(sql, "TopTen", DBOpt.Oracle); ;
        }

        protected string getOptionCriteria(string p_opt)
        {
            string[] opts = p_opt.Split(new char[] { '_' });
            string v1, v2;
            v2 = string.Empty;
            v1 = opts[0].Equals("單筆") ? "AND (B.SIP_TYPE_CODE IS NULL)"
                : " AND (B.SIP_TYPE_CODE IN (1,2,3,4,5))";
            switch (opts[1].ToString())
            { 
                case "網銀":
                    v2 = " AND (B.Channel_Code = 'ELECW') ";
                    break;
                case "客服專員":
                    v2 = " AND (B.Channel_Code = 'ELECT' OR B.Channel_Code = 'ELECP') ";
                    break;
                case "語音":
                    v2 = " AND (B.Channel_Code = 'ELECV') ";
                    break;
                case "其他/臨櫃":
                    v2 = " AND (NOT B.Channel_Code LIKE 'ELE%' OR B.Channel_Code IS NULL) ";
                    break;
            }
            return v1+v2;
        }

        protected string getFieldString(string[] p_months)
        {
            string fld = @"
                 ,MAX(CASE WHEN A.SNAP_DATE = '{0}' THEN A.TXN_AMT END) Y{0}
";
            return getFieldString(p_months, fld);
        }

        protected string getFieldString(string[] p_months, string p_fld)
        {
            //StringBuilder sb = new StringBuilder();
            //foreach (string mon in p_months)
            //{
            //    sb.AppendFormat(p_fld, mon);
            //}
            //return sb.ToString();
            return Utility.GetYearMonthString(p_months, p_fld);
        }


        protected string getSummeryStatement()
        {
            /*
             * {0} = AND A.SNAP_DATE BETWEEN TO_DATE('20110131','YYYYMMDD') AND TO_DATE('20110430','YYYYMMDD')
             * 	,MAX(CASE WHEN A.SNAP_DATE = '201101' THEN A.TXN_AMT END) Y201101
	            ,MAX(CASE WHEN A.SNAP_DATE = '201102' THEN A.TXN_AMT END) Y201102
	            ,MAX(CASE WHEN A.SNAP_DATE = '201103' THEN A.TXN_AMT END) Y201103
	            ,MAX(CASE WHEN A.SNAP_DATE = '201104' THEN A.TXN_AMT END) Y201104

             */
            return @"
SELECT
	A.Channel
	{1}
FROM(
	SELECT
		CASE 
			WHEN (A.Channel IS NULL) AND (A.GRP_ID = 1) AND (A.TYPE_CODE='1') THEN '單筆小計'
			WHEN (A.Channel IS NULL) AND (A.GRP_ID = 1) AND (A.TYPE_CODE='2') THEN '定期小計'
			WHEN (A.Channel IS NULL) AND (A.GRP_ID = 1) AND (A.TYPE_CODE IS NULL) THEN '合計'    
		ELSE A.Channel END Channel
		,A.SNAP_DATE
		,TO_CHAR(A.TXN_AMT, 'FM999,999,999,999,999') 	TXN_AMT        
	FROM X_F_B_MF_MONTHLY_TXNS A
	WHERE A.GRP_ID IN(0,1,3)
    {0}
)A
WHERE NOT A.Channel IS NULL
GROUP BY A.channel
ORDER BY CASE 
			WHEN A.Channel = '單筆_網銀' THEN 100 
			WHEN A.Channel = '單筆_客服專員' THEN 200
			WHEN A.Channel = '單筆_語音' THEN 300
			WHEN A.Channel = '單筆_其他/臨櫃' THEN 400
			WHEN A.Channel = '單筆小計' THEN 450
			WHEN A.Channel = '定期_網銀' THEN 500
			WHEN A.Channel = '定期_客服專員' THEN 600
			WHEN A.Channel = '定期_語音' THEN 700
			WHEN A.Channel = '定期_其他/臨櫃' THEN 800
			WHEN A.Channel = '定期小計' THEN 850	END
";
        }

        protected string getSQLStatementByPAY()
        {
            /*
             * {0} WHERE
             * {1} FIELD
             * {2} UNION 用來建造出薪轉、其它
             */
            return @"
SELECT
	NVL(A.CUST_TYPE,'合計')CUST_TYPE
	{1}
FROM(
	SELECT
		A.CUST_TYPE
		,A.SNAP_DATE
		,TO_CHAR(A.TXN_AMT, 'FM999,999,999,999,999') TXN_AMT
	FROM(	
		SELECT
			A.CUST_TYPE
			,A.SNAP_DATE
			,SUM(A.TXN_AMT) TXN_AMT
			,GROUPING_ID(A.SNAP_DATE,A.CUST_TYPE)GRP_ID
		FROM(
			SELECT
				CASE WHEN Customer_Type_Code = '9' THEN '薪轉'
					ELSE '其它'
				END CUST_TYPE			
				,TO_CHAR(A.SNAP_DATE,'YYYYMM')SNAP_DATE
				,A.TXN_AMT
			FROM DM_F_VIEW.F_B_MF_MONTHLY_TXNS A LEFT OUTER JOIN DM_F_VIEW.D_B_MF_CERTIFICATE B
				ON A.mutual_fund_certificate_nbr = B.mutual_fund_certificate_nbr
			WHERE 1=1			
			{0}
			
			{2}
		)A	GROUP BY CUBE(A.SNAP_DATE,A.CUST_TYPE)
	)A WHERE A.GRP_ID IN (0,1)	
)A GROUP BY A.CUST_TYPE
ORDER BY CASE 
  WHEN A.CUST_TYPE = '薪轉' THEN 100
  WHEN A.CUST_TYPE = '其它' THEN 200
  ELSE 300 END
";
        }

        /// <summary>
        /// 產生出兩筆資料，避免撈資料時，沒有薪轉戶
        /// </summary>
        /// <param name="p_month">某一個月份</param>
        /// <returns></returns>
        protected string getUNIONStatement(string p_month)
        {
            return string.Format(@"
			UNION ALL
			SELECT 	'其它' ,'{0}' ,0 FROM DUAL
			UNION ALL
			SELECT 	'薪轉' ,'{0}' ,0 FROM DUAL
",p_month);
        }

        protected string getTopTenStatement()
        {
            /*	
    ,MAX(CASE WHEN A.YM = '201106' THEN A.基金名稱 END) AS 基金名稱201106
	,MAX(CASE WHEN A.YM = '201106' THEN A.銷量 END) AS 銷量201106
	,MAX(CASE WHEN A.YM = '201107' THEN A.基金名稱 END) AS 基金名稱201107
	,MAX(CASE WHEN A.YM = '201107' THEN A.銷量 END) AS 銷量201107 

             */
            return @"
SELECT
    A.SEQ  
    {1}
FROM X_F_B_MF_MONTHLY_TXNS_TOP10 A
WHERE A.SEQ < 51
{0}
GROUP BY A.SEQ
ORDER BY A.SEQ
";

        }

        
    }
}
